﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Text;

namespace HZ.Data
{
    using System.Data.OracleClient;
    public class OracleHelper : IDbHelper, IDisposable
    {
        private OracleConnection con;
        public static string ConnectionString = null;

        #region 构造函数

        public OracleHelper()
        {
            if (ConnectionString != null)
            {
                con = new OracleConnection(ConnectionString);
                return;
            }
            string constr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
            if (string.IsNullOrEmpty(constr))
            {
                throw new Exception("数据库连接不存在,请检查App.config文件");
            }
            else
            {
                con = new OracleConnection(constr);
                ConnectionString = constr;
            }
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectstring">指定连接字符串</param>
        public OracleHelper(string connectstring)
        {
            con = new OracleConnection(connectstring);
            ConnectionString = connectstring;
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            if (con != null && con.State != ConnectionState.Closed)
            {
                con.Close();
            }
            GC.SuppressFinalize(this);
        }

        #endregion

        #region IDbHelper Members

        void IDbHelper.Close()
        {
            Dispose();
        }
        /// <summary>
        /// 执行一个T-SQL (不返回结果集)
        /// </summary>
        /// <param name="cmdText">T-SQL 命令</param>
        /// <returns>返回一个 int 表示这个命令所影响的行数</returns>
        public int ExecuteNonQuery(string cmdText)
        {
            return ExecuteNonQuery(CommandType.Text, cmdText, null);
        }
        /// <summary>
        ///执行一个T-SQL命令(可以含参数)(不返回结果集) 
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commandType">命令类型 (存储过程, 文本, 等)</param>
        /// <param name="commandText">存储过程名称 or T-SQL 命令</param>
        /// <param name="commandParameters">一组用于执行该命令的参数</param>
        /// <returns>返回执行这个命令所影响的行数</returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText, params IDataParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();

            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                PrepareCommand(cmd, conn, commandType, commandText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }
        /// <summary>
        /// 执行一个T-SQL命令(返回结果集)
        /// </summary>
        /// <param name="cmdText">T-SQL 命令</param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string cmdText)
        {
            return ExecuteReader(CommandType.Text, cmdText, null);
        }
        /// <summary>
        /// 执行一个T-SQL命令(返回结果集)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks> 
        /// <param name="commandType">命令类型(存储过程, 文本, 等)</param>
        /// <param name="commandText">存储过程名称 or T-SQL 命令</param>
        /// <param name="commandParameters">一组用于执行该命令的参数</param>
        /// <returns>一个包含查询结果的 DataReader</returns>
        public IDataReader ExecuteReader(CommandType commandType, string commandText, params IDataParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(ConnectionString);

            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, commandType, commandText, commandParameters);
                OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return (IDataReader)rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }
        /// <summary>
        /// 执行一个命令(返回结果集的第一行第一列结果)
        /// </summary>
        /// <param name="cmdText">T-SQL 命令</param>
        /// <returns>返回一个可以转换成预期类型的object对象</returns>
        public object ExecuteScalar(string cmdText)
        {
            return ExecuteScalar(CommandType.Text, cmdText, null);
        }
        /// <summary>
        /// 执行一个命令(返回结果集的第一行第一列结果)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks> 
        /// <param name="commandType">命令类型(存储过程, 文本, 等)</param>
        /// <param name="commandText">存储过程名称 or T-SQL 命令</param>
        /// <param name="commandParameters">一组用于执行该命令的参数</param>
        /// <returns>返回一个可以转换成预期类型的object对象</returns>
        public object ExecuteScalar(CommandType commandType, string commandText, params IDataParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();

            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                PrepareCommand(cmd, connection, commandType, commandText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }
        /// <summary>
        /// 执行一个T-SQL命令(返回一个DataTable)
        /// </summary>
        /// <param name="cmdText">T-SQL 命令</param>
        /// <returns>返回包含结果的DataTable </returns>
        public DataTable ExecuteTable(string cmdText)
        {
            return ExecuteTable(CommandType.Text, cmdText, null);
        }
        /// <summary>
        /// 执行一个T-SQL命令(返回一个DataTable)
        /// </summary>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等)</param>
        /// <param name="cmdText">存储过程名称 or T-SQL 命令</param>
        /// <param name="commandParameters">一组用于执行该命令的参数</param>
        /// <returns>返回包含结果的DataTable</returns>
        public DataTable ExecuteTable(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();

            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, commandParameters);
                OracleDataAdapter adp = new OracleDataAdapter(cmd);
                DataTable dt = new DataTable();
                adp.Fill(dt);
                return dt;
            }
        }
        /// <summary>
        /// 执行一个T-SQL命令(返回一个DataSet)
        /// </summary>
        /// <param name="cmdText">T-SQL 命令</param>
        /// <returns>返回一个 DataSet</returns>
        public DataSet ExecuteDataSet(string cmdText)
        {
            return ExecuteDataSet(CommandType.Text, cmdText, null);
        }
        /// <summary>
        /// 执行一个T-SQL命令(返回一个DataSet)
        /// </summary>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等)</param>
        /// <param name="cmdText">存储过程名称 or T-SQL 命令</param>
        /// <param name="commandParameters">一组用于执行该命令的参数</param>
        /// <returns>返回一个 DataSet</returns>
        public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();

            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, commandParameters);
                OracleDataAdapter adp = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                return ds;
            }
        }

        #endregion

        #region 私有函数

        /// <summary>
        /// OracleCommand准备
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private void PrepareCommand(OracleCommand cmd, OracleConnection conn, CommandType cmdType, string cmdText, IDataParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (IDataParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        private void OpenConnection()
        {
            if (con.State != ConnectionState.Open)
                con.Open();
        }

        #endregion

    }
}
